
/*remise dans le bon sens de masque-var*/
-- masque_var_trans;


define erp='erp_mli2.';
define outils='outils_mli2.';


 /*
select * from (
SELECT &outils.stragg(source) source,champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
FROM (
  SELECT 'uv' source ,g.* FROM se_GEN_CHAMPCTAV g where exists (select 1 from se_GEN_CHAMPCTAV gg where gg.champsais_cod=g.champsais_cod)

  UNION ALL
  SELECT 'se'
      --  || v.vrt_cod || v.att_cod|| (select &outils.stragg(valeur_val) from &erp.gen_champctavval vv where vv.champsais_cod=g.champsais_cod)
      source ,g.* FROM &erp.GEN_CHAMPCTAV g,&erp.ART_GENECHAPATT v where v.champsais_cod=g.champsais_cod
  )

GROUP BY champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
HAVING Count(1)!=2 ) r
ORDER BY CHAMPCTAV_TYP,champsais_cod,source  */

 delete from se_GEN_CHAMPCTAV;

 INSERT INTO se_GEN_CHAMPCTAV
 (SELECT m.champsais_cod,
         g.champctav_typ,
         0 enreg_id,
         g.grille_cod,
         g.bornemsg_lib,
         g.complsqlgrille_lib,
         g.complsqltable_lib,
         g.entitecle_cod,
         g.entiteent_cod,
         g.entiteslig_cod,
         g.grillechamp_cod,
         'N' enreg_s,
         g.formatcol_val,
         g.msg_lib,
         g.borne_typ,
         g.borneinf_val,
         g.bornesup_val,
         g.valeur_typ,
         g.msgvaleur_lib
     FROM  se_ART_GENECHAPATT m , se_VRT_ATT v,se_GEN_CHAMPCTAV_vrtatt g
     WHERE m.vrt_cod=v.vrt_cod AND m.vrtattatt_cod=v.att_cod  AND g.champsais_cod=v.champsais_cod

     );


 UPDATE se_GEN_CHAMPCTAV tf SET enreg_id=Nvl((select enreg_id FROM &erp.GEN_CHAMPCTAV f WHERE f.champsais_cod=tf.champsais_cod and f.CHAMPCTAV_TYP=tf.CHAMPCTAV_TYP),0) ;
 UPDATE se_GEN_CHAMPCTAV tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;





 commit;





